DECLARE num NUMBER;
BEGIN
SELECT COUNT(1) INTO num FROM USER_TABLES WHERE TABLE_NAME = UPPER('TB_AC_CLIENT') ;
IF num > 0 THEN
EXECUTE IMMEDIATE 'DROP TABLE TB_AC_CLIENT' ;
END IF;
END;
/

DECLARE
  C NUMBER;
BEGIN
SELECT COUNT(*) INTO C
FROM ALL_SEQUENCES
  WHERE SEQUENCE_NAME = 'SEQ_TB_AC_CLIENT_ID';
  IF (C > 0) THEN
    EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_TB_AC_CLIENT_ID';
END IF;
END
;
/
CREATE TABLE TB_AC_CLIENT
(
	id NUMBER(38) NOT NULL,
	client_id VARCHAR2(64) NOT NULL,
	name VARCHAR2(64) NOT NULL,
	secret VARCHAR2(255),
	remark VARCHAR2(500),
	status VARCHAR2(10) default '0',
	add_time NUMBER(13),
	ext  CLOB,
	white_ip VARCHAR2(1000),
	domain VARCHAR2(255)
);

COMMENT ON TABLE TB_AC_CLIENT IS '统一认证客户端'
;

COMMENT ON COLUMN TB_AC_CLIENT.client_id IS '客户端ID'
;

COMMENT ON COLUMN TB_AC_CLIENT.name IS '客户端名称'
;

COMMENT ON COLUMN TB_AC_CLIENT.name IS '秘钥'
;

COMMENT ON COLUMN TB_AC_CLIENT.remark IS '备注'
;

COMMENT ON COLUMN TB_AC_CLIENT.status IS '状态，1:启用，0:停用'
;

COMMENT ON COLUMN TB_AC_CLIENT.add_time IS '添加时间'
;

COMMENT ON COLUMN TB_AC_CLIENT.ext IS '参数配置'
;

COMMENT ON COLUMN TB_AC_CLIENT.white_ip IS 'IP白名单'
;

COMMENT ON COLUMN TB_AC_CLIENT.domain IS '域名地址'
;


ALTER TABLE TB_AC_CLIENT
 ADD CONSTRAINT PK_TB_AC_CLIENT
  PRIMARY KEY (id) USING INDEX
;

CREATE SEQUENCE SEQ_TB_AC_CLIENT_ID
  INCREMENT BY 1
  START WITH 1
  NOMAXVALUE
  MINVALUE  1
  NOCYCLE
  NOCACHE
  NOORDER
;
CREATE OR REPLACE TRIGGER TRG_TB_AC_CLIENT_ID
  BEFORE INSERT
  ON TB_AC_CLIENT
  FOR EACH ROW
  BEGIN
    SELECT SEQ_TB_AC_CLIENT_ID.NEXTVAL
    INTO :NEW.ID
    FROM DUAL;
  END;
